#include "formpzdr.h"
#include "ui_formpzdr.h"
#include <mysql.h>
#include <QClipboard>

FormPZDR::FormPZDR(QWidget *parent) :
    QWidget(parent),
    ui(new Ui::FormPZDR)
{
    ui->setupUi(this);
    this->show();
    Start();

}

FormPZDR::~FormPZDR()
{
    delete ui;
}

void FormPZDR::Start(){
    QStringList list1;
    list1.clear();
    list1<<"会计年"<<"会计月"<<"凭证类型"<<"凭证日期"<<"凭证号"<<"附件张数"<<"科目代码"<<"科目名称"<<"借贷"<<"项目编号"<<"项目名称"<<"货币"<<"原币"<<"本币"<<"摘要";
    ui->tableWidget->clear();
    ui->tableWidget->setColumnCount(list1.size());
    ui->tableWidget->setHorizontalHeaderLabels(list1);
    ui->cmbPZNY->clear();
    QString SQL="";
    SQL=QString("SELECT COUNT(*) AS 行数, jzny FROM pzzb WHERE type_id='%1' GROUP BY jzny ORDER BY jzny DESC ").arg(mySQL::TypeId);
    QSqlQuery query(mySQL::cn); //查询 表并输出，测试能否正常操作数据库
    query.exec(SQL);
    //qDebug() << SQL;
    while(query.next()){
        //QMessageBox::warning(this,"警告",query.value("t").toString());
        ui->cmbPZNY->addItem(query.value("jzny").toString());
    }
}

void FormPZDR::on_btnPaste_clicked()
{
    Start();
    QString tmp="";
    tmp="[会计年]，[会计月]，[凭证类型]，[凭证日期]，[凭证号]，[附件张数]，[科目代码]，[科目名称]，[借贷]，[项目编号]，[项目名称]，[货币]，[原币]，[本币]，[摘要]";
    QMessageBox message(QMessageBox::Warning,"请确认",QString("确认粘贴数据的格式为 \n%1?").arg(tmp),QMessageBox::Yes|QMessageBox::No,NULL);
        if (message.exec()==QMessageBox::Yes)
        {
            QClipboard *board = QApplication::clipboard();
            QString str = board->text();
            //QMessageBox::information(NULL, "From clipboard", str);
            QStringList strList = str.split("\n");
            QSqlQuery query(mySQL::cn); //查询 表并输出，测试能否正常操作数据库
            for(int i = 0; i < strList.size()-1; i++) {

                QStringList subList = strList[i].split("\t");
                if(subList[0]!="会计年"){
                    int row = ui->tableWidget->rowCount();
                    ui->tableWidget->insertRow(row);
                    for(int j = 0; j < subList.size(); j++) {
                        ui->tableWidget->setItem(row,j,new QTableWidgetItem(subList[j]));
                    }
                }
                }

        }
}

void FormPZDR::on_btnPZCHK_clicked()
{
    //下面开始检查每一条凭证的借贷是否平衡
    QString CHKJG="";
    QString PZH="";
    QString PZH1="";
    QString JD="";
    double JF=0;
    double DF=0;
    for(int i=0;i<ui->tableWidget->rowCount();i++){
        if(ui->tableWidget->item(i,0)!=NULL && ui->tableWidget->item(i,0)->text().trimmed()!="" ){
            if (PZH==""){
                PZH=ui->tableWidget->item(i,0)->text().trimmed();
                PZH+=ui->tableWidget->item(i,1)->text().trimmed();
                PZH+=ui->tableWidget->item(i,2)->text().trimmed();
                PZH+=ui->tableWidget->item(i,4)->text().trimmed();
            }
            if (PZH!=PZH1){
                if (PZH1!=""){
                    if (QString::number(JF-DF, 'f', 2)!="0.00"){
                        CHKJG+=QString("%1 借贷不平！(%L2)\n").arg(PZH1).arg(JF-DF, 3, 'f', 2);
                    }
                }
                PZH1=PZH;
                JF=0;
                DF=0;
            }
            JD=ui->tableWidget->item(i,8)->text().trimmed();
            if (JD=="借"){
                JF+=ui->tableWidget->item(i,13)->text().toDouble();
            }
            if (JD=="贷"){
                DF+=ui->tableWidget->item(i,13)->text().toDouble();
            }
        }
    }
    if (QString::number(JF-DF, 'f', 2)!="0.00"){
        CHKJG+=QString("%1 借贷不平！(%L2)\n").arg(PZH1).arg(JF-DF, 3, 'f', 2);
    }
    QMessageBox::warning(this,"警告",CHKJG);
}

void FormPZDR::on_btnPZimp_clicked()
{
    QString SQL="";
    QString ND="";
    int PZBH=0;
    QString JZNY="";
    QSqlQuery query(mySQL::cn); //查询 表并输出，测试能否正常操作数据库

    QString CHKJG="";
    QString PZH="";
    QString PZH1="";
    QString JD="";
    ui->progressBar->setMaximum(ui->tableWidget->rowCount());
    for(int i=0;i<ui->tableWidget->rowCount();i++){
        if(ui->tableWidget->item(i,0)!=NULL && ui->tableWidget->item(i,0)->text().trimmed()!="" ){
            PZH=ui->tableWidget->item(i,0)->text().trimmed();
            PZH+=ui->tableWidget->item(i,1)->text().trimmed();
            PZH+=ui->tableWidget->item(i,2)->text().trimmed();
            PZH+=ui->tableWidget->item(i,4)->text().trimmed();
            ND=ui->tableWidget->item(i,0)->text().trimmed();
            JZNY=ui->tableWidget->item(i,0)->text().trimmed();
            if (ui->tableWidget->item(i,1)->text().length()==1){
                JZNY+="0";
            }
            JZNY+=ui->tableWidget->item(i,1)->text().trimmed();
            if (PZH!=PZH1){
                if (PZBH==0){
                    SQL="select max(pzbh) as t from pzzb ";
                    SQL+=QString(" where type_id='%1' and nd='%2'  order by nd desc ").arg(mySQL::TypeId).arg(ND);
                    query.exec(SQL);
                    qDebug() << SQL;
                    while(query.next()){
                        //QMessageBox::warning(this,"警告",query.value("t").toString());
                        PZBH=query.value("t").toInt();
                        //QMessageBox::warning(this,"警告",QString("最大凭证编号：%1").arg(PZBH));
                    }
                }
                PZBH++; //凭证编号+1 获取新的凭证号


                SQL="INSERT INTO pzzb(type_id,nd,jzny,pzbh,pzlx,jzrq,dj,jz,pzxx,fhrq,fh,xgrq) VALUES (";
                SQL+=QString("'%1',").arg(mySQL::TypeId.trimmed().replace("'","''"));
                SQL+=QString("'%1',").arg(ND.trimmed().replace("'","''"));
                SQL+=QString("'%1',").arg(JZNY.trimmed().replace("'","''"));
                SQL+=QString("%1,").arg(PZBH);
                SQL+=QString("'%1',").arg(ui->tableWidget->item(i,2)->text().trimmed());
                SQL+=QString("'%1',").arg(ui->tableWidget->item(i,3)->text().trimmed().replace("/","-"));
                SQL+=QString("'%1',").arg(ui->tableWidget->item(i,4)->text().trimmed().replace("'","''"));
                SQL+=QString("'%1',").arg(mySQL::UserName.trimmed().replace("'","''"));
                SQL+=QString("'%1',").arg(PZH.trimmed().replace("'","''"));
                SQL+="'2000-01-01',";
                SQL+="'',";
                SQL+=QString("'%1')").arg(QDateTime::currentDateTime().toString("yyyy-MM-dd hh:mm:ss"));
                //QMessageBox::warning(this,"警告",SQL);
                mySQL::cn.exec(SQL);
                PZH1=PZH;

            }
            JD=ui->tableWidget->item(i,8)->text().trimmed();
            double HL=0;
            if (ui->tableWidget->item(i,12)->text().toDouble()!=0){
                HL=ui->tableWidget->item(i,13)->text().toDouble()/ui->tableWidget->item(i,12)->text().toDouble();
            }
            QString HB=ui->tableWidget->item(i,11)->text();
            HB=HB.replace("人民币","CNY");
            HB=HB.replace("美元","USD");
            HB=HB.replace("欧元","EUR");
            HB=HB.replace("英镑","GBP");
            HB=HB.replace("日元","JPY");

            //凭证ID为空则insert into

            SQL="INSERT INTO pz(type_id,nd,jzny,pzbh,pzlx,kmdm,xmbh,jd,lc,zyxx,je,yb,hb,hl,rq,jz) VALUES (";
            SQL+=QString("'%1',").arg(mySQL::TypeId.trimmed().replace("'","''"));
            SQL+=QString("'%1',").arg(ND.trimmed().replace("'","''"));
            SQL+=QString("'%1',").arg(JZNY.trimmed().replace("'","''"));
            SQL+=QString("%1,").arg(PZBH);
            SQL+=QString("'%1',").arg(ui->tableWidget->item(i,2)->text().trimmed().replace("'","''"));
            SQL+=QString("'%1',").arg(ui->tableWidget->item(i,6)->text().trimmed().replace("'","''"));
            SQL+=QString("'%1',").arg(ui->tableWidget->item(i,9)->text().trimmed().replace(".","").replace("'","''"));
            SQL+=QString("'%1',").arg(JD.trimmed().replace("'","''"));
            SQL+=QString("'%1',").arg("");
            SQL+=QString("'%1',").arg(ui->tableWidget->item(i,14)->text().trimmed().replace("'","''"));
            SQL+=QString("%1,").arg(QString::number(ui->tableWidget->item(i,13)->text().toDouble(), 'f', 4));
            SQL+=QString("%1,").arg(QString::number(ui->tableWidget->item(i,12)->text().toDouble(), 'f', 4));
            SQL+=QString("'%1',").arg(HB.replace("'","''"));
            SQL+=QString("'%1',").arg(HL);
            SQL+=QString("'%1',").arg(ui->tableWidget->item(i,3)->text().trimmed().replace("/","-"));
            SQL+=QString("'%1')").arg(mySQL::UserName.trimmed().replace("'","''"));
            mySQL::cn.exec(SQL);

        }
        ui->progressBar->setValue(i);
        ui->progressBar->update();

    }
    ui->progressBar->setValue(ui->progressBar->maximum());
    QMessageBox::warning(this,"警告","导入成功！");


}

void FormPZDR::on_btnPZDEL_clicked()
{
    QString str="";
    str=QString("确认删除记账年月为【%1】的凭证吗？ 删除后是无法恢复的！！！！").arg(ui->cmbPZNY->currentText());
    if (QMessageBox::information(NULL, "提醒", str,QMessageBox::Yes | QMessageBox::No, QMessageBox::No)==QMessageBox::Yes){
        QString SQL="";
        QSqlQuery query(mySQL::cn); //查询 表并输出，测试能否正常操作数据库
        SQL=QString("delete from  pzzb where type_id='%1' and jzny='%2'").arg(mySQL::TypeId).arg(ui->cmbPZNY->currentText());
        query.exec(SQL);
        SQL=QString("delete from  pz where type_id='%1' and jzny='%2'").arg(mySQL::TypeId).arg(ui->cmbPZNY->currentText());
        query.exec(SQL);
        QMessageBox::warning(this,"警告","删除成功！");
        Start();
    }

}
